package edu.byu.intex2.data.dao;

import edu.byu.intex2.data.bo.Customer;
import edu.byu.intex2.data.Cache;
import edu.byu.intex2.data.ConnectionPool;
import edu.byu.intex2.data.DataException;
import java.sql.*;
import edu.byu.intex2.data.bo.*;
import edu.byu.intex2.data.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 * Customer Data Access Object
 * @author Brady
 */
public class CustomerDAO {

  /////////////////////////////////////////////
  ///   Singleton code

  private static CustomerDAO instance = null;

  /** Creates a new instance of CustomerDAO */
  private CustomerDAO() {
  }//constructor

  /** Retrieves the single instance of this class */
  public static synchronized CustomerDAO getInstance() {
    if (instance == null) {
      instance = new CustomerDAO();
    }
    return instance;
  }//getInstance

  ////////////////////////////////////////////
  ///   CREATE methods

  /** 
   * Creates a new customer in the database
   * set alreadyInDB to false
   * put customer in cache
   */
  public Customer create(String id) throws DataException {
        Customer cust = new Customer(GUID.generate());
        cust.setAlreadyInDB(false);
        Cache.getInstance().put(cust.getId(), cust);
        return cust;
  }//create


  ////////////////////////////////////////////
  ///   READ methods

  /** 
   * Reads an existing customer from the database
   * 1) Checks cache
   * 2) Gets a connection
   * 3) Calls read with connection argument
   */
  public Customer read(String id) throws DataException {
      if(Cache.getInstance().containsKey(id)){
          return (Customer)Cache.getInstance().get(id);
      }
      Connection conn = ConnectionPool.getInstance().get();
      try{
          return read(id, conn);
      }
      catch(Exception e){
          throw new DataException("Could not read the Customer Object, e");
      }
  }

  /** 
   * Internal method to read an existing customer from the database
   * 1) Check cache for object
   * 2) Create a prepared statement to execute sql query
   * 3) Create a resultset from the preparedstatement
   * 4) Return the customer
   */
  synchronized Customer read(String id, Connection conn) throws Exception {
      if(Cache.getInstance().containsKey(id)){
            return (Customer)Cache.getInstance().get(id);
      }
      PreparedStatement sql = conn.prepareStatement("SELECT * FROM customer WHERE id=?");
      try{
        sql.setString(1, id);
        ResultSet rs = sql.executeQuery();
        if(rs.next()){
            return readRecord(rs, conn);
        }
      throw new DataException("could not read the Customer");
      }     
      finally {sql.close();}
  }//read


  /** 
   * Internal method to create a customer object from a record
   * 1) Check cache for object
   * 2) Instantiate new customer
   * 3) Set alreadyInDb to false
   * 4) Set all variables from result set
   * 5) Set dirty to true
   * 6) Return customer
   */
  synchronized Customer readRecord(ResultSet rs, Connection conn) throws Exception {
        if(Cache.getInstance().containsKey(rs.getString("id"))){
            return (Customer)Cache.getInstance().get(rs.getString("id"));
        }
        Customer cust = new Customer(rs.getString("id"));
        cust.setAlreadyInDB(true);
        Cache.getInstance().put(cust.getId(), cust);
        cust.setHighPrice(Double.parseDouble(rs.getString("high_price")));
        cust.setLowPrice(Double.parseDouble(rs.getString("low_price")));
        
        return cust;


  }//readRecord  

  /////////////////////////////////////////////
  ///   UPDATE methods
  
  /** Saves an existing customer in the database */
  public void save(Customer customer) throws DataException {
    Connection conn = ConnectionPool.getInstance().get();
    try {
      save(customer, conn);
      conn.commit();
    }catch (Exception e) {
      try{
        conn.rollback();
      }catch (SQLException e2) {
        throw new DataException("Could not roll back the database transaction!", e2);
      }
      throw new DataException("An error occurred while saving the business object information.", e);
    }finally {
      ConnectionPool.getInstance().release(conn);
    }
	
  }
  
  /** Internal method to update a customer in the database */
  void save(Customer customer, Connection conn) throws Exception {
    Cache.getInstance().put(customer.getId(), customer);

    if (customer.alreadyInDB() && customer.isDirty()) {
      update(customer, conn);
    }else if (customer.isDirty()){
      insert(customer, conn);
    }//if
	
    }  
  
  /** Saves an existing customer to the database */
  private void update(Customer customer, Connection conn) throws Exception {
     PreparedStatement stmt = conn.prepareStatement("UPDATE day_of_week SET description=? WHERE id=?");
    try {
      stmt.setString(1, customer.getName());
      stmt.setString(2, customer.getId());
      stmt.execute();
      dayofweek.setDirty(false);
    }finally{
      stmt.close();
    }
	
  }
  
  /** Inserts a new customer into the database */
  private void insert(Customer customer, Connection conn) throws Exception {
    //use a perpared statement
	//set the alreadyInDB variable to true
	//dirty to false
	//close the prepared statement
	
  }  
  
  
  
  
  
  /////////////////////////////////////////////////
  ///   DELETE methods
  
  /** We do not support deleting of business objects in this application */
  public void delete(Customer customer) throws DataException {
    throw new RuntimeException("Nice try. The delete function is not supported in this application.");
  }




  ////////////////////////////////////////////////
  ///   SEARCH methods
 /** Internal method to search by certain fields */
  
  public List<Customer> getAll() throws DataException {
       Connection conn = ConnectionPool.getInstance().get();
       
       try {            
            PreparedStatement sql = conn.prepareStatement("SELECT * FROM customer");            
            return search(sql, conn);
       }
       catch (Exception e) {
            try{
                conn.rollback();
            }catch (SQLException e2) {
                throw new DataException("Could not roll back the database transaction!", e2);
            }      
            throw new DataException("An error occurred while reading the business object information.", e);    
            }
            finally {      
                ConnectionPool.getInstance().release(conn);
            } 
   }
  
  private List<Customer> search(PreparedStatement stmt, Connection conn) throws Exception {
  
    List<Customer> custList = new ArrayList<Customer>();
    try {
      ResultSet rs = stmt.executeQuery();
      while (rs.next()) {
        custList.add(readRecord(rs, conn));
      }
    }finally{
      stmt.close();
    }
    return custList;
  }

  public List<Customer> getByName(String fName, String lName) throws DataException{

      Connection conn = ConnectionPool.getInstance().get();

       try {
            PreparedStatement sql = conn.prepareStatement("SELECT * FROM customer c INNER JOIN person p ON p.id = c.id WHERE p.fname=? AND p.lname=?");
            sql.setString(1, fName);
            sql.setString(2, lName);
            return search(sql, conn);
       }
       catch (Exception e) {
            try{
                conn.rollback();
            }catch (SQLException e2) {
                throw new DataException("Could not roll back the database transaction!", e2);
            }
            throw new DataException("An error occurred while reading the business object information.", e);
            }
            finally {
                ConnectionPool.getInstance().release(conn);
            }
  } 
       
}